﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class FieldDutyTreeDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public FieldDutyTreeDAL()
        {

        }

        public IList<FieldDutyTreeInfo> GetFieldDutyTree()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<FieldDutyTreeInfo> infoList = new List<FieldDutyTreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT '0' AS ID, NULL AS PARENT_ID" // Header
                        + ", 'หน้าที่ความรับผิดชอบ' AS NAME"
                        + ", NULL AS DESCR"
                        + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT('1', '-', FIELD_POS_SEQ) AS CHAR) AS ID"
                        + ", '0' AS PARENT_ID" // ตำแหน่งสายงาน
                        + ", FIELD_POS_NAME AS NAME"
                        + ", REMARKS AS DESCR"
                        + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                    + " FROM HRM_MP_FIELD_POSITIONS"
                    + " WHERE RECORD_STATUS not like @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT('2', '-', fd.CLASS_SEQ) AS CHAR) AS ID" // ระดับ
                        + ", CAST(fd.FIELD_POS_SEQ AS CHAR) AS PARENT_ID"
                        + ", c.CLASS_NAME AS NAME"
                        + ", fd.REMARKS AS DESCR"
                        + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                    + " FROM HRM_MP_FIELD_DUTIES fd"
                        + " LEFT JOIN HRM_MP_CLASSES c ON fd.CLASS_SEQ = c.CLASS_SEQ"
                    + " WHERE fd.RECORD_STATUS not like @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT('3', '-', d.DUTY_SEQ) AS CHAR) AS ID" // ด้านความรับผิดชอบ
                        + ", CAST(fd.CLASS_SEQ AS CHAR) AS PARENT_ID"
                        + ", dt.DUTY_NAME AS NAME"
                        + ", '' AS DESCR"
                        + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                    + " FROM HRM_MP_FIELD_DUTY_DTLS d"
                        + " INNER JOIN HRM_MP_FIELD_DUTIES fd ON d.FIELD_DUTY_SEQ = fd.FIELD_DUTY_SEQ"
                        + " INNER JOIN HRM_MP_DUTIES dt ON d.DUTY_SEQ = dt.DUTY_SEQ"
                    + " WHERE fd.RECORD_STATUS not like @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT('4', '-', d.FIELD_DUTY_DTLS_SEQ) AS CHAR) AS ID" // หน้าที่ความรับผิดชอบ
                        + ", CAST(d.FIELD_DUTY_SEQ AS CHAR) AS PARENT_ID"
                        + ", d.DESCR AS NAME"
                        + ", '' AS DESCR"
                        + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                    + " FROM HRM_MP_FIELD_DUTY_DTLS d"
                        + " INNER JOIN HRM_MP_FIELD_DUTIES fd ON d.FIELD_DUTY_SEQ = fd.FIELD_DUTY_SEQ"
                    + " WHERE d.RECORD_STATUS not like @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        FieldDutyTreeInfo info = new FieldDutyTreeInfo();
                        info.ID = (dr[i]["ID"]).ToString();
                        info.Parent = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCR"]) ? null : (dr[i]["DESCR"]).ToString();
                        info.ImageUrl = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
        public IList<FieldDutyTreeInfo> GetFieldDutyTree(string parent_id)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<FieldDutyTreeInfo> infoList = new List<FieldDutyTreeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@parent_id", parent_id);

                strSQL = "SELECT main.* FROM " // Header
                        + "(SELECT CAST(CONCAT('0', '-', POS_TYPE_SEQ) AS CHAR) AS ID"
                        + ", NULL AS PARENT_ID"
                        + ", CONCAT('ตำแหน่งประเภท', POS_TYPE_NAME) AS NAME"
                        + ", NULL AS DESCR"
                        + ", (select count(field_pos_seq) from hrm_mp_field_positions "
                            + " where record_status not like @delete_status"
                            + " and pos_type_seq = pt.pos_type_seq"
                        + ") AS CHILDREN_COUNT"
                        + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                    + " FROM HRM_MP_POSITION_TYPES pt"
                    + " WHERE pt.RECORD_STATUS not like @delete_status"
                        + " AND pt.EMP_TYPE_SEQ = 1"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT('1', '-', FIELD_POS_SEQ) AS CHAR) AS ID"
                        + ", CAST(CONCAT('0-', POS_TYPE_SEQ) AS CHAR) AS PARENT_ID" // ตำแหน่งสายงาน
                        + ", FIELD_POS_NAME AS NAME"
                        + ", REMARKS AS DESCR"
                        + ", (select count(class_seq) from hrm_mp_field_duties "
                            + " where record_status not like @delete_status"
                            + " and field_pos_seq = hrm_mp_field_positions.field_pos_seq "
                        + ") AS CHILDREN_COUNT"
                        + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                    + " FROM HRM_MP_FIELD_POSITIONS"
                    + " WHERE RECORD_STATUS not like @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT('2', '-', fd.FIELD_DUTY_SEQ) AS CHAR) AS ID" // ระดับ
                        + ", CAST(CONCAT('1-', FIELD_POS_SEQ) AS CHAR) AS PARENT_ID"
                        + ", CLASS_NAME AS NAME"
                        + ", fd.REMARKS AS DESCR"
                        + ", (select count(duty_seq) from hrm_mp_field_duty_dtls "
                            + " where record_status not like @delete_status"
                            + " and field_duty_seq = fd.field_duty_seq) AS CHILDREN_COUNT"
                        + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                    + " FROM HRM_MP_FIELD_DUTIES fd"
                        + " LEFT JOIN HRM_MP_CLASSES c ON fd.CLASS_SEQ = c.CLASS_SEQ"
                    + " WHERE fd.RECORD_STATUS not like @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT('3', '-', d.DUTY_SEQ) AS CHAR) AS ID" // ด้านความรับผิดชอบ
                        + ", CAST(CONCAT('2-', fd.CLASS_SEQ) AS CHAR) AS PARENT_ID"
                        + ", dt.DUTY_NAME AS NAME"
                        + ", '' AS DESCR"
                        + ", (select count(field_duty_dtls_seq) from hrm_mp_field_duty_dtls"
                            + " where record_status not like @delete_status"
                            + " and duty_seq = hrm_mp_field_duty_dtls.duty_seq"
                        + ") AS CHILDREN_COUNT"
                        + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                    + " FROM HRM_MP_FIELD_DUTY_DTLS d"
                        + " INNER JOIN HRM_MP_FIELD_DUTIES fd ON d.FIELD_DUTY_SEQ = fd.FIELD_DUTY_SEQ"
                        + " INNER JOIN HRM_MP_DUTIES dt ON d.DUTY_SEQ = dt.DUTY_SEQ"
                    + " WHERE fd.RECORD_STATUS not like @delete_status"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT('4', '-', d.FIELD_DUTY_DTLS_SEQ) AS CHAR) AS ID" // หน้าที่ความรับผิดชอบ
                        + ", CAST(CONCAT('3-', d.FIELD_DUTY_SEQ) AS CHAR) AS PARENT_ID"
                        + ", CAST(CONCAT(d.FIELD_DUTY_DTLS_SEQ, '-', d.DESCR) AS CHAR) AS NAME"
                        + ", '' AS DESCR"
                        + ", 0 AS CHILDREN_COUNT"
                        + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                    + " FROM HRM_MP_FIELD_DUTY_DTLS d"
                        + " INNER JOIN HRM_MP_FIELD_DUTIES fd ON d.FIELD_DUTY_SEQ = fd.FIELD_DUTY_SEQ"
                    + " WHERE d.RECORD_STATUS not like @delete_status"
                    + ") main";
                if (parent_id == null || parent_id.Trim() == "")
                {
                    strSQL += " WHERE PARENT_ID IS NULL ";
                }
                else 
                {
                    strSQL += " WHERE PARENT_ID  = @parent_id  ";
                }

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        FieldDutyTreeInfo info = new FieldDutyTreeInfo();
                        info.ID = (dr[i]["ID"]).ToString();
                        info.Parent = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? null : (dr[i]["PARENT_ID"]).ToString();
                        info.Name = (dr[i]["NAME"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCR"]) ? null : (dr[i]["DESCR"]).ToString();
                        info.ImageUrl = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();
                        info.ChildrenCount = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
    }
}
